﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
			   SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/




DECLARE @LeadTypes TABLE (LeadTypeKey int, LeadType nVarChar(20))

INSERT INTO @LeadTypes (LeadTypeKey, LeadType) Values
(0, 'Raw Lead'),
(1, 'News Report'),
(2, 'Article'),
(3, 'Interview')

MERGE 
	INTO LeadType as Target
	USING @LeadTypes as Source ON Target.LeadTypeKey = Source.LeadTypeKey
	WHEN MATCHED THEN
		UPDATE SET LeadType = Source.LeadType
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (LeadTypeKey, LeadType) VALUES (Source.LeadTypeKey, Source.LeadType)
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE;

DECLARE @LeadStatus TABLE (LeadStatusKey int, LeadStatus nVarChar(20), IsActive bit)

INSERT INTO @LeadStatus (LeadStatusKey, LeadStatus, IsActive) Values
(0, 'Proposed', 0),
(1, 'Claimed', 1),
(2, 'Ready for Review', 1),
(3, 'Published', 0)

MERGE 
	INTO LeadStatus as Target
	USING @LeadStatus as Source ON Target.LeadStatusKey = Source.LeadStatusKey
	WHEN MATCHED THEN
		UPDATE SET LeadStatus = Source.LeadStatus, IsActive = Source.IsActive
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (LeadStatusKey, LeadStatus, IsActive) VALUES (Source.LeadStatusKey, Source.LeadStatus, Source.IsActive)
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE;

